package com.next

import grails.converters.JSON
import org.springframework.security.access.annotation.Secured

import java.text.SimpleDateFormat

import static org.springframework.http.HttpStatus.*

@Secured(['ROLE_ADMINISTRATOR', 'ROLE_COMPANY_ADMINISTRATOR', 'ROLE_COMPANY_USER'])
class SalesDashboard2Controller
{
    def contactService
    def opportunityService
    def springSecurityService

    def dashboard()
    {
        def username = springSecurityService?.getPrincipal()?.username

        if (UserRole.findByUserAndRole(User.findByUsername(username), Role.findByAuthority('ROLE_ADMINISTRATOR')))
        {
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd")
            SimpleDateFormat sdf2 = new SimpleDateFormat("yyyy-MM")
            Calendar currentDay = Calendar.getInstance()
            Calendar currentWeek = Calendar.getInstance()
            Calendar currentMonth = Calendar.getInstance()
            Calendar currentSeason = Calendar.getInstance()
            Calendar currentYear = Calendar.getInstance()
            Calendar endWeek = Calendar.getInstance()
            Calendar endMonth = Calendar.getInstance()
            Calendar endSeason = Calendar.getInstance()
            Calendar endYear = Calendar.getInstance()
            int season = Math.floor((currentSeason.get(Calendar.MONTH)) / 3)
            currentWeek.set(Calendar.DAY_OF_WEEK, 1)
            currentWeek.add(Calendar.DATE, -6)

            currentMonth.set(Calendar.DAY_OF_MONTH, 1)
            currentSeason.set(Calendar.DAY_OF_MONTH, 1)
            currentSeason.set(Calendar.MONTH, 3 * season)
            currentYear.set(Calendar.DAY_OF_YEAR, 1)
            // endWeek.set(Calendar.DAY_OF_WEEK, 1)
            endMonth.set(Calendar.DAY_OF_MONTH, 1)
            endMonth.add(Calendar.MONTH, 1)
            endSeason.set(Calendar.DAY_OF_MONTH, 1)
            if (season == 4)
            {
                endSeason.set(Calendar.MONTH, 0)
                endSeason.add(Calendar.YEAR, 1)
            }
            else
            {
                endSeason.set(Calendar.MONTH, 3 * season)
            }
            endYear.set(Calendar.DAY_OF_YEAR, 1)
            endYear.add(Calendar.YEAR, 1)
            String cDay = sdf.format(currentDay.getTime())
            String cWeek = sdf.format(currentWeek.getTime())
            String cMonth = sdf.format(currentMonth.getTime())
            String cSeason = sdf.format(currentSeason.getTime())
            String cYear = sdf.format(currentYear.getTime())
            String eWeek = sdf.format(endWeek.getTime())
            String eMonth = sdf.format(endMonth.getTime())
            String eSeason = sdf.format(endSeason.getTime())
            String eYear = sdf.format(endYear.getTime())

            def startTime = currentWeek.getTime()
            def endtTime = endWeek.getTime()

            def accountStartTime = params['accountStartTime'] ?: startTime
            def accountEndTime = params['accountEndTime'] ?: endtTime
            def leadStartTime = params['leadStartTime'] ?: startTime
            def leadEndTime = params['leadEndTime'] ?: endtTime
            def activityStartTime = params['activityStartTime'] ?: startTime
            def activityEndTime = params['activityEndTime'] ?: endtTime
            def opportunityStartTime = params['opportunityStartTime'] ?: startTime
            def opportunityEndTime = params['opportunityEndTime'] ?: endtTime
            def noteStartTime = params['noteStartTime'] ?: startTime
            def noteEndTime = params['noteEndTime'] ?: endtTime
            def activityStatus = params['activityStatus']
            def opportunityStatus = params['opportunityStatus']
            def accountSql
            def leadSql
            def activitySql
            def opportunitySql
            def noteSql

            def departmentStatistics = []
            def departmentlist = Department.findAllByActive(true)
            def accountCount
            def leadCount
            def activityCount
            def opportunityCount
            def noteCount

            def totalStatistic = [:]
            accountSql = "select count(id) from Account where user.department.active = true and modifiedDate between '${accountStartTime}' and '${accountEndTime}'"
            leadSql = "select count(id) from Lead where user.department.active = true and modifiedDate between '${leadStartTime}' and '${leadEndTime}'"
            activitySql = "select count(id) from Activity where user.department.active = true and modifiedDate between '${activityStartTime}' and '${activityEndTime}'"
            if (activityStatus)
            {
                activitySql += " and status = '${activityStatus}'"
            }
            opportunitySql = "select count(id) from Opportunity where user.department.active = true and modifiedDate between '${opportunityStartTime}' and '${opportunityEndTime}'"
            if (opportunityStatus)
            {
                opportunitySql += " and status.name = '${opportunityStatus}'"
            }
            noteSql = "select count(id) from Note where user.department.active = true and modifiedDate between '${noteStartTime}' and '${noteEndTime}'"

            accountCount = Account.executeQuery(accountSql)
            leadCount = Lead.executeQuery(leadSql)
            activityCount = Activity.executeQuery(activitySql)
            opportunityCount = Opportunity.executeQuery(opportunitySql)
            noteCount = Note.executeQuery(noteSql)

            totalStatistic['accountCount'] = accountCount[0]
            totalStatistic['leadCount'] = leadCount[0]
            totalStatistic['activityCount'] = activityCount[0]
            totalStatistic['opportunityCount'] = opportunityCount[0]
            totalStatistic['noteCount'] = noteCount[0]

            departmentlist?.each { department ->
                accountSql = "select count(id) from Account where user.department.id = ${department?.id} and modifiedDate between '${accountStartTime}' and '${accountEndTime}'"
                leadSql = "select count(id) from Lead where user.department.id = ${department?.id} and modifiedDate between '${leadStartTime}' and '${leadEndTime}'"
                activitySql = "select count(id) from Activity where user.department.id = ${department?.id} and modifiedDate between '${activityStartTime}' and '${activityEndTime}'"
                if (activityStatus)
                {
                    activitySql += " and status = '${activityStatus}'"
                }
                opportunitySql = "select count(id) from Opportunity where user.department.id = ${department?.id} and modifiedDate between '${opportunityStartTime}' and '${opportunityEndTime}'"
                if (opportunityStatus)
                {
                    opportunitySql += " and status.name = '${opportunityStatus}'"
                }
                noteSql = "select count(id) from Note where user.department.id = ${department?.id} and modifiedDate between '${noteStartTime}' and '${noteEndTime}'"

                accountCount = Account.executeQuery(accountSql)
                leadCount = Lead.executeQuery(leadSql)
                activityCount = Activity.executeQuery(activitySql)
                opportunityCount = Opportunity.executeQuery(opportunitySql)
                noteCount = Note.executeQuery(noteSql)

                def item = [:]
                item['departmentId'] = department?.id
                item['department'] = department?.name
                item['accountCount'] = accountCount[0]
                if (totalStatistic['accountCount'] > 0)
                {
                    item['accountPercent'] = (accountCount[0] / totalStatistic['accountCount']).setScale(4, BigDecimal.ROUND_HALF_UP).doubleValue() * 100
                }
                else
                {
                    item['accountPercent'] = 0
                }
                item['leadCount'] = leadCount[0]
                if (totalStatistic['leadCount'] > 0)
                {
                    item['leadPercent'] = (leadCount[0] / totalStatistic['leadCount']).setScale(4, BigDecimal.ROUND_HALF_UP).doubleValue() * 100
                }
                else
                {
                    item['leadPercent'] = 0
                }
                item['activityCount'] = activityCount[0]
                if (totalStatistic['activityCount'] > 0)
                {
                    item['activityPercent'] = (activityCount[0] / totalStatistic['activityCount']).setScale(4, BigDecimal.ROUND_HALF_UP).doubleValue() * 100
                }
                else
                {
                    item['activityPercent'] = 0
                }
                item['opportunityCount'] = opportunityCount[0]
                if (totalStatistic['opportunityCount'] > 0)
                {
                    item['opportunityPercent'] = (opportunityCount[0] / totalStatistic['opportunityCount']).setScale(4, BigDecimal.ROUND_HALF_UP).doubleValue() * 100
                }
                else
                {
                    item['opportunityPercent'] = 0
                }
                item['noteCount'] = noteCount[0]
                if (totalStatistic['noteCount'] > 0)
                {
                    item['notePercent'] = (noteCount[0] / totalStatistic['noteCount']).setScale(4, BigDecimal.ROUND_HALF_UP).doubleValue() * 100
                }
                else
                {
                    item['notePercent'] = 0
                }

                departmentStatistics.add(item)
            }

            respond totalStatistic, model: [totalStatistic: totalStatistic, departmentStatistics: departmentStatistics]
        }
        else if (UserRole.findByUserAndRole(User.findByUsername(username), Role.findByAuthority('ROLE_SALES_MANAGER')))
        {
            def currentDepartment = User.findByUsername(username)?.department
            redirect action: "departmentList", params: [department: currentDepartment?.id]
            return
        }
        else
        {
            redirect action: "personalList"
            return
        }

    }

    def departmentList()
    {
        def username = springSecurityService?.getPrincipal()?.username

        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd")
        SimpleDateFormat sdf2 = new SimpleDateFormat("yyyy-MM")
        Calendar currentDay = Calendar.getInstance()
        Calendar currentWeek = Calendar.getInstance()
        Calendar currentMonth = Calendar.getInstance()
        Calendar currentSeason = Calendar.getInstance()
        Calendar currentYear = Calendar.getInstance()
        Calendar endWeek = Calendar.getInstance()
        Calendar endMonth = Calendar.getInstance()
        Calendar endSeason = Calendar.getInstance()
        Calendar endYear = Calendar.getInstance()
        int season = Math.floor((currentSeason.get(Calendar.MONTH)) / 3)
        currentWeek.set(Calendar.DAY_OF_WEEK, 1)
        currentWeek.add(Calendar.DATE, -6)

        currentMonth.set(Calendar.DAY_OF_MONTH, 1)
        currentSeason.set(Calendar.DAY_OF_MONTH, 1)
        currentSeason.set(Calendar.MONTH, 3 * season)
        currentYear.set(Calendar.DAY_OF_YEAR, 1)
        // endWeek.set(Calendar.DAY_OF_WEEK, 1)
        endMonth.set(Calendar.DAY_OF_MONTH, 1)
        endMonth.add(Calendar.MONTH, 1)
        endSeason.set(Calendar.DAY_OF_MONTH, 1)
        if (season == 4)
        {
            endSeason.set(Calendar.MONTH, 0)
            endSeason.add(Calendar.YEAR, 1)
        }
        else
        {
            endSeason.set(Calendar.MONTH, 3 * season)
        }
        endYear.set(Calendar.DAY_OF_YEAR, 1)
        endYear.add(Calendar.YEAR, 1)
        String cDay = sdf.format(currentDay.getTime())
        String cWeek = sdf.format(currentWeek.getTime())
        String cMonth = sdf.format(currentMonth.getTime())
        String cSeason = sdf.format(currentSeason.getTime())
        String cYear = sdf.format(currentYear.getTime())
        String eWeek = sdf.format(endWeek.getTime())
        String eMonth = sdf.format(endMonth.getTime())
        String eSeason = sdf.format(endSeason.getTime())
        String eYear = sdf.format(endYear.getTime())

        def startTime = currentWeek.getTime()
        def endtTime = endWeek.getTime()

        def accountStartTime = params['accountStartTime'] ?: startTime
        def accountEndTime = params['accountEndTime'] ?: endtTime
        def leadStartTime = params['leadStartTime'] ?: startTime
        def leadEndTime = params['leadEndTime'] ?: endtTime
        def activityStartTime = params['activityStartTime'] ?: startTime
        def activityEndTime = params['activityEndTime'] ?: endtTime
        def opportunityStartTime = params['opportunityStartTime'] ?: startTime
        def opportunityEndTime = params['opportunityEndTime'] ?: endtTime
        def noteStartTime = params['noteStartTime'] ?: startTime
        def noteEndTime = params['noteEndTime'] ?: endtTime
        def activityStatus = params['activityStatus']
        def opportunityStatus = params['opportunityStatus']
        def accountSql
        def leadSql
        def activitySql
        def opportunitySql
        def noteSql

        def departmentId = params?.department
        def department
        def totalStatistic = [:]
        def accountList
        def leadList
        def activityList
        def opportunityList
        def noteList
        if (departmentId)
        {
            department = Department.findById(departmentId)

            totalStatistic['department'] = department?.id
            if (UserRole.findByUserAndRole(User.findByUsername(username), Role.findByAuthority('ROLE_ADMINISTRATOR')))
            {
                totalStatistic['isAdmin'] = true
            }
            else
            {
                totalStatistic['isAdmin'] = false
            }

            accountSql = "from Account where user.department.id = ${department?.id} and modifiedDate between '${accountStartTime}' and '${accountEndTime}'"
            leadSql = "from Lead where user.department.id = ${department?.id} and modifiedDate between '${leadStartTime}' and '${leadEndTime}'"
            activitySql = "from Activity where user.department.id = ${department?.id} and modifiedDate between '${activityStartTime}' and '${activityEndTime}'"
            if (activityStatus)
            {
                activitySql += " and status = '${activityStatus}'"
            }
            opportunitySql = "from Opportunity where user.department.id = ${department?.id} and modifiedDate between '${opportunityStartTime}' and '${opportunityEndTime}'"
            if (opportunityStatus)
            {
                opportunitySql += " and status.name = '${opportunityStatus}'"
            }
            noteSql = "from Note where user.department.id = ${department?.id} and modifiedDate between '${noteStartTime}' and '${noteEndTime}'"

            accountList = Account.findAll(accountSql)
            leadList = Lead.findAll(leadSql)
            activityList = Activity.findAll(activitySql)
            opportunityList = Opportunity.findAll(opportunitySql)
            noteList = Note.findAll(noteSql)
        }

        respond totalStatistic, model: [totalStatistic: totalStatistic, accountList: accountList, leadList: leadList, activityList: activityList, opportunityList: opportunityList, noteList: noteList]
    }

    def personalList()
    {
        def username = springSecurityService?.getPrincipal()?.username

        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd")
        SimpleDateFormat sdf2 = new SimpleDateFormat("yyyy-MM")
        Calendar currentDay = Calendar.getInstance()
        Calendar currentWeek = Calendar.getInstance()
        Calendar currentMonth = Calendar.getInstance()
        Calendar currentSeason = Calendar.getInstance()
        Calendar currentYear = Calendar.getInstance()
        Calendar endWeek = Calendar.getInstance()
        Calendar endMonth = Calendar.getInstance()
        Calendar endSeason = Calendar.getInstance()
        Calendar endYear = Calendar.getInstance()
        int season = Math.floor((currentSeason.get(Calendar.MONTH)) / 3)
        currentWeek.set(Calendar.DAY_OF_WEEK, 1)
        currentWeek.add(Calendar.DATE, -6)

        currentMonth.set(Calendar.DAY_OF_MONTH, 1)
        currentSeason.set(Calendar.DAY_OF_MONTH, 1)
        currentSeason.set(Calendar.MONTH, 3 * season)
        currentYear.set(Calendar.DAY_OF_YEAR, 1)
        // endWeek.set(Calendar.DAY_OF_WEEK, 1)
        endMonth.set(Calendar.DAY_OF_MONTH, 1)
        endMonth.add(Calendar.MONTH, 1)
        endSeason.set(Calendar.DAY_OF_MONTH, 1)
        if (season == 4)
        {
            endSeason.set(Calendar.MONTH, 0)
            endSeason.add(Calendar.YEAR, 1)
        }
        else
        {
            endSeason.set(Calendar.MONTH, 3 * season)
        }
        endYear.set(Calendar.DAY_OF_YEAR, 1)
        endYear.add(Calendar.YEAR, 1)
        String cDay = sdf.format(currentDay.getTime())
        String cWeek = sdf.format(currentWeek.getTime())
        String cMonth = sdf.format(currentMonth.getTime())
        String cSeason = sdf.format(currentSeason.getTime())
        String cYear = sdf.format(currentYear.getTime())
        String eWeek = sdf.format(endWeek.getTime())
        String eMonth = sdf.format(endMonth.getTime())
        String eSeason = sdf.format(endSeason.getTime())
        String eYear = sdf.format(endYear.getTime())

        def startTime = currentWeek.getTime()
        def endtTime = endWeek.getTime()

        def accountStartTime = params['accountStartTime'] ?: startTime
        def accountEndTime = params['accountEndTime'] ?: endtTime
        def leadStartTime = params['leadStartTime'] ?: startTime
        def leadEndTime = params['leadEndTime'] ?: endtTime
        def activityStartTime = params['activityStartTime'] ?: startTime
        def activityEndTime = params['activityEndTime'] ?: endtTime
        def opportunityStartTime = params['opportunityStartTime'] ?: startTime
        def opportunityEndTime = params['opportunityEndTime'] ?: endtTime
        def noteStartTime = params['noteStartTime'] ?: startTime
        def noteEndTime = params['noteEndTime'] ?: endtTime
        def activityStatus = params['activityStatus']
        def opportunityStatus = params['opportunityStatus']
        def accountSql
        def leadSql
        def activitySql
        def opportunitySql
        def noteSql

        def user = User.findByUsername(username)
        def accountCount
        def leadCount
        def activityCount
        def opportunityCount
        def noteCount

        def accountList
        def leadList
        def activityList
        def opportunityList
        def noteList

        def totalStatistic = [:]

        accountSql = "select count(id) from Account where user.id = ${user?.id} and modifiedDate between '${accountStartTime}' and '${accountEndTime}'"
        leadSql = "select count(id) from Lead where user.id = ${user?.id} and modifiedDate between '${leadStartTime}' and '${leadEndTime}'"
        activitySql = "select count(id) from Activity where user.id = ${user?.id} and modifiedDate between '${activityStartTime}' and '${activityEndTime}'"
        if (activityStatus)
        {
            activitySql += " and status = '${activityStatus}'"
        }
        opportunitySql = "select count(id) from Opportunity where user.id = ${user?.id} and modifiedDate between '${opportunityStartTime}' and '${opportunityEndTime}'"
        if (opportunityStatus)
        {
            opportunitySql += " and status.name = '${opportunityStatus}'"
        }
        noteSql = "select count(id) from Note where user.id = ${user?.id} and modifiedDate between '${noteStartTime}' and '${noteEndTime}'"

        accountCount = Account.executeQuery(accountSql)
        leadCount = Lead.executeQuery(leadSql)
        activityCount = Activity.executeQuery(activitySql)
        opportunityCount = Opportunity.executeQuery(opportunitySql)
        noteCount = Note.executeQuery(noteSql)

        accountSql = "select count(id) from Account where user.department.id = ${user?.department?.id} and modifiedDate between '${accountStartTime}' and '${accountEndTime}'"
        leadSql = "select count(id) from Lead where user.department.id = ${user?.department?.id} and modifiedDate between '${leadStartTime}' and '${leadEndTime}'"
        activitySql = "select count(id) from Activity where user.department.id = ${user?.department?.id} and modifiedDate between '${activityStartTime}' and '${activityEndTime}'"
        if (activityStatus)
        {
            activitySql += " and status = '${activityStatus}'"
        }
        opportunitySql = "select count(id) from Opportunity where user.department.id = ${user?.department?.id} and modifiedDate between '${opportunityStartTime}' and '${opportunityEndTime}'"
        if (opportunityStatus)
        {
            opportunitySql += " and status.name = '${opportunityStatus}'"
        }
        noteSql = "select count(id) from Note where user.department.id = ${user?.department?.id} and modifiedDate between '${noteStartTime}' and '${noteEndTime}'"

        def departmentAccount = Account.executeQuery(accountSql)
        def departmentLead = Lead.executeQuery(leadSql)
        def departmentActivity = Activity.executeQuery(activitySql)
        def departmentOpportunity = Opportunity.executeQuery(opportunitySql)
        def departmentNote = Note.executeQuery(noteSql)

        accountSql = "from Account where user.id = ${user?.id} and modifiedDate between '${accountStartTime}' and '${accountEndTime}'"
        leadSql = "from Lead where user.id = ${user?.id} and modifiedDate between '${leadStartTime}' and '${leadEndTime}'"
        activitySql = "from Activity where user.id = ${user?.id} and modifiedDate between '${activityStartTime}' and '${activityEndTime}'"
        if (activityStatus)
        {
            activitySql += " and status = '${activityStatus}'"
        }
        opportunitySql = "from Opportunity where user.id = ${user?.id} and modifiedDate between '${opportunityStartTime}' and '${opportunityEndTime}'"
        if (opportunityStatus)
        {
            opportunitySql += " and status.name = '${opportunityStatus}'"
        }
        noteSql = "from Note where user.id = ${user?.id} and modifiedDate between '${noteStartTime}' and '${noteEndTime}'"

        accountList = Account.findAll(accountSql)
        leadList = Lead.findAll(leadSql)
        activityList = Activity.findAll(activitySql)
        opportunityList = Opportunity.findAll(opportunitySql)
        noteList = Note.findAll(noteSql)

        totalStatistic['accountCount'] = accountCount[0]
        if (departmentAccount[0] > 0)
        {
            totalStatistic['accountPercent'] = (totalStatistic['accountCount'] / departmentAccount[0]).setScale(4, BigDecimal.ROUND_HALF_UP).doubleValue() * 100
        }
        else
        {
            totalStatistic['accountPercent'] = 0
        }
        totalStatistic['leadCount'] = leadCount[0]
        if (departmentLead[0] > 0)
        {
            totalStatistic['leadPercent'] = (totalStatistic['leadCount'] / departmentLead[0]).setScale(4, BigDecimal.ROUND_HALF_UP).doubleValue() * 100
        }
        else
        {
            totalStatistic['leadPercent'] = 0
        }
        totalStatistic['activityCount'] = activityCount[0]
        if (departmentActivity[0] > 0)
        {
            totalStatistic['activityPercent'] = (totalStatistic['activityCount'] / departmentActivity[0]).setScale(4, BigDecimal.ROUND_HALF_UP).doubleValue() * 100
        }
        else
        {
            totalStatistic['activityPercent'] = 0
        }
        totalStatistic['opportunityCount'] = opportunityCount[0]
        if (departmentOpportunity[0] > 0)
        {
            totalStatistic['opportunityPercent'] = (totalStatistic['opportunityCount'] / departmentOpportunity[0]).setScale(4, BigDecimal.ROUND_HALF_UP).doubleValue() * 100
        }
        else
        {
            totalStatistic['opportunityPercent'] = 0
        }
        totalStatistic['noteCount'] = noteCount[0]
        if (departmentNote[0] > 0)
        {
            totalStatistic['notePercent'] = (totalStatistic['noteCount'] / departmentNote[0]).setScale(4, BigDecimal.ROUND_HALF_UP).doubleValue() * 100
        }
        else
        {
            totalStatistic['notePercent'] = 0
        }

        respond totalStatistic, model: [totalStatistic: totalStatistic, accountList: accountList, leadList: leadList, activityList: activityList, opportunityList: opportunityList, noteList: noteList]
    }

    def loadTotalData()
    {
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd")
        SimpleDateFormat sdf2 = new SimpleDateFormat("yyyy-MM")
        Calendar currentDay = Calendar.getInstance()
        Calendar currentWeek = Calendar.getInstance()
        Calendar currentMonth = Calendar.getInstance()
        Calendar currentSeason = Calendar.getInstance()
        Calendar currentYear = Calendar.getInstance()
        Calendar endWeek = Calendar.getInstance()
        Calendar endMonth = Calendar.getInstance()
        Calendar endSeason = Calendar.getInstance()
        Calendar endYear = Calendar.getInstance()
        int season = Math.floor((currentSeason.get(Calendar.MONTH)) / 3)
        currentWeek.set(Calendar.DAY_OF_WEEK, 1)
        currentWeek.add(Calendar.DATE, -6)

        currentMonth.set(Calendar.DAY_OF_MONTH, 1)
        currentSeason.set(Calendar.DAY_OF_MONTH, 1)
        currentSeason.set(Calendar.MONTH, 3 * season)
        currentYear.set(Calendar.DAY_OF_YEAR, 1)
        // endWeek.set(Calendar.DAY_OF_WEEK, 1)
        endMonth.set(Calendar.DAY_OF_MONTH, 1)
        endMonth.add(Calendar.MONTH, 1)
        endSeason.set(Calendar.DAY_OF_MONTH, 1)
        if (season == 4)
        {
            endSeason.set(Calendar.MONTH, 0)
            endSeason.add(Calendar.YEAR, 1)
        }
        else
        {
            endSeason.set(Calendar.MONTH, 3 * season)
        }
        endYear.set(Calendar.DAY_OF_YEAR, 1)
        endYear.add(Calendar.YEAR, 1)
        String cDay = sdf.format(currentDay.getTime())
        String cWeek = sdf.format(currentWeek.getTime())
        String cMonth = sdf.format(currentMonth.getTime())
        String cSeason = sdf.format(currentSeason.getTime())
        String cYear = sdf.format(currentYear.getTime())
        String eWeek = sdf.format(endWeek.getTime())
        String eMonth = sdf.format(endMonth.getTime())
        String eSeason = sdf.format(endSeason.getTime())
        String eYear = sdf.format(endYear.getTime())

        def startTime = currentWeek.getTime()
        def endtTime = endWeek.getTime()

        def accountStartTime = params['accountStartTime'] ?: startTime
        def accountEndTime = params['accountEndTime'] ?: endtTime
        def leadStartTime = params['leadStartTime'] ?: startTime
        def leadEndTime = params['leadEndTime'] ?: endtTime
        def activityStartTime = params['activityStartTime'] ?: startTime
        def activityEndTime = params['activityEndTime'] ?: endtTime
        def opportunityStartTime = params['opportunityStartTime'] ?: startTime
        def opportunityEndTime = params['opportunityEndTime'] ?: endtTime
        def noteStartTime = params['noteStartTime'] ?: startTime
        def noteEndTime = params['noteEndTime'] ?: endtTime
        def activityStatus = params['activityStatus']
        def opportunityStatus = params['opportunityStatus']
        def accountSql
        def leadSql
        def activitySql
        def opportunitySql
        def noteSql

        def departmentStatistics = []
        def departmentlist = Department.findAllByActive(true)
        def accountCount
        def leadCount
        def activityCount
        def opportunityCount
        def noteCount

        departmentlist?.each { department ->
            accountSql = "select count(id) from Account where user.department.id = ${department?.id} and modifiedDate between '${accountStartTime}' and '${accountEndTime}'"
            leadSql = "select count(id) from Lead where user.department.id = ${department?.id} and modifiedDate between '${leadStartTime}' and '${leadEndTime}'"
            activitySql = "select count(id) from Activity where user.department.id = ${department?.id} and modifiedDate between '${activityStartTime}' and '${activityEndTime}'"
            if (activityStatus)
            {
                activitySql += " and status = '${activityStatus}'"
            }
            opportunitySql = "select count(id) from Opportunity where user.department.id = ${department?.id} and modifiedDate between '${opportunityStartTime}' and '${opportunityEndTime}'"
            if (opportunityStatus)
            {
                opportunitySql += " and status.name = '${opportunityStatus}'"
            }
            noteSql = "select count(id) from Note where user.department.id = ${department?.id} and modifiedDate between '${noteStartTime}' and '${noteEndTime}'"

            accountCount = Account.executeQuery(accountSql)
            leadCount = Lead.executeQuery(leadSql)
            activityCount = Activity.executeQuery(activitySql)
            opportunityCount = Opportunity.executeQuery(opportunitySql)
            noteCount = Note.executeQuery(noteSql)

            def item = [:]
            item['department'] = department?.name
            item['accountCount'] = accountCount[0]
            item['leadCount'] = leadCount[0]
            item['activityCount'] = activityCount[0]
            item['opportunityCount'] = opportunityCount[0]
            item['noteCount'] = noteCount[0]

            departmentStatistics.add(item)
        }

        render([departmentStatistics: departmentStatistics] as JSON)
    }

    def loadDepartmentData()
    {
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd")
        SimpleDateFormat sdf2 = new SimpleDateFormat("yyyy-MM")
        Calendar currentDay = Calendar.getInstance()
        Calendar currentWeek = Calendar.getInstance()
        Calendar currentMonth = Calendar.getInstance()
        Calendar currentSeason = Calendar.getInstance()
        Calendar currentYear = Calendar.getInstance()
        Calendar endWeek = Calendar.getInstance()
        Calendar endMonth = Calendar.getInstance()
        Calendar endSeason = Calendar.getInstance()
        Calendar endYear = Calendar.getInstance()
        int season = Math.floor((currentSeason.get(Calendar.MONTH)) / 3)
        currentWeek.set(Calendar.DAY_OF_WEEK, 1)
        currentWeek.add(Calendar.DATE, -6)

        currentMonth.set(Calendar.DAY_OF_MONTH, 1)
        currentSeason.set(Calendar.DAY_OF_MONTH, 1)
        currentSeason.set(Calendar.MONTH, 3 * season)
        currentYear.set(Calendar.DAY_OF_YEAR, 1)
        // endWeek.set(Calendar.DAY_OF_WEEK, 1)
        endMonth.set(Calendar.DAY_OF_MONTH, 1)
        endMonth.add(Calendar.MONTH, 1)
        endSeason.set(Calendar.DAY_OF_MONTH, 1)
        if (season == 4)
        {
            endSeason.set(Calendar.MONTH, 0)
            endSeason.add(Calendar.YEAR, 1)
        }
        else
        {
            endSeason.set(Calendar.MONTH, 3 * season)
        }
        endYear.set(Calendar.DAY_OF_YEAR, 1)
        endYear.add(Calendar.YEAR, 1)
        String cDay = sdf.format(currentDay.getTime())
        String cWeek = sdf.format(currentWeek.getTime())
        String cMonth = sdf.format(currentMonth.getTime())
        String cSeason = sdf.format(currentSeason.getTime())
        String cYear = sdf.format(currentYear.getTime())
        String eWeek = sdf.format(endWeek.getTime())
        String eMonth = sdf.format(endMonth.getTime())
        String eSeason = sdf.format(endSeason.getTime())
        String eYear = sdf.format(endYear.getTime())

        def startTime = currentWeek.getTime()
        def endtTime = endWeek.getTime()

        def accountStartTime = params['accountStartTime'] ?: startTime
        def accountEndTime = params['accountEndTime'] ?: endtTime
        def leadStartTime = params['leadStartTime'] ?: startTime
        def leadEndTime = params['leadEndTime'] ?: endtTime
        def activityStartTime = params['activityStartTime'] ?: startTime
        def activityEndTime = params['activityEndTime'] ?: endtTime
        def opportunityStartTime = params['opportunityStartTime'] ?: startTime
        def opportunityEndTime = params['opportunityEndTime'] ?: endtTime
        def noteStartTime = params['noteStartTime'] ?: startTime
        def noteEndTime = params['noteEndTime'] ?: endtTime
        def activityStatus = params['activityStatus']
        def opportunityStatus = params['opportunityStatus']
        def accountSql
        def leadSql
        def activitySql
        def opportunitySql
        def noteSql

        def departmentId = params?.department
        def department
        def userList = []
        def accountCount
        def leadCount
        def activityCount
        def opportunityCount
        def noteCount
        def userStatistics = []
        if (departmentId)
        {
            department = Department.findById(departmentId)
            userList = User.findAllByDepartment(department)
            userList?.each { user ->
                accountSql = "select count(id) from Account where user.id = ${user?.id} and modifiedDate between '${accountStartTime}' and '${accountEndTime}'"
                leadSql = "select count(id) from Lead where user.id = ${user?.id} and modifiedDate between '${leadStartTime}' and '${leadEndTime}'"
                activitySql = "select count(id) from Activity where user.id = ${user?.id} and modifiedDate between '${activityStartTime}' and '${activityEndTime}'"
                if (activityStatus)
                {
                    activitySql += " and status = '${activityStatus}'"
                }
                opportunitySql = "select count(id) from Opportunity where user.id = ${user?.id} and modifiedDate between '${opportunityStartTime}' and '${opportunityEndTime}'"
                if (opportunityStatus)
                {
                    opportunitySql += " and status.name = '${opportunityStatus}'"
                }
                noteSql = "select count(id) from Note where user.id = ${user?.id} and modifiedDate between '${noteStartTime}' and '${noteEndTime}'"

                accountCount = Account.executeQuery(accountSql)
                leadCount = Lead.executeQuery(leadSql)
                activityCount = Activity.executeQuery(activitySql)
                opportunityCount = Opportunity.executeQuery(opportunitySql)
                noteCount = Note.executeQuery(noteSql)

                def item = [:]
                item['user'] = user?.fullName
                item['accountCount'] = accountCount[0]
                item['leadCount'] = leadCount[0]
                item['activityCount'] = activityCount[0]
                item['opportunityCount'] = opportunityCount[0]
                item['noteCount'] = noteCount[0]
                userStatistics.add(item)
            }
        }
        render([userStatistics: userStatistics] as JSON)
    }

}
